/*
 * DBAccess.java
 * 
 * Data access for SQLite database.
 * 
 * Copyright 2013 Santi Ruiz Andrés <starfly1570@gmail.com>
 * 
 * This file is part of Drawer of Marks.
 * 
 * Drawer of Marks is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 * 
 * Drawer of Marks is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 * 
 * You should have received a copy of the GNU General Public License
 * along with Drawer of Marks.  If not, see <http://www.gnu.org/licenses/>.
 * 
 */

package blogspot.santiruizpro.drawerofmarks.model;

import java.util.ArrayList;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

/**
 * @author starfly1570
 */
public class DBAccess {

	public static final String DATABASE_NAME = "marks.db";
	private Context context;
	private SQLiteDatabase db;
	
	public DBAccess(Context context) {
		this.context = context;
		initDB();
	}
	
	/**
	 * Creates all tables of database if they don't exists.
	 */
	private void initDB() {
		openDB();
		db.execSQL("Create table if not exists subjects(pkey integer PRIMARY KEY AUTOINCREMENT, name varchar(100), isweighted integer);");
		db.execSQL("Create table if not exists divisions(pkey integer PRIMARY KEY AUTOINCREMENT, name varchar(100), isweighted integer, weight float, subjectkey integer, FOREIGN KEY (subjectkey) references subjects(pkey) ON DELETE CASCADE);");
		db.execSQL("Create table if not exists exams(pkey integer PRIMARY KEY AUTOINCREMENT, name varchar(100), mark float, weight float, divisionkey integer, FOREIGN KEY (divisionkey) references divisions(pkey) ON DELETE CASCADE);");
		
		// Column added since version 3
		Cursor query = db.rawQuery("select * from exams", null);
		if ( query.getColumnCount() < 6 ) {
			db.execSQL("alter table exams add is_active integer;");
			db.execSQL("update exams set is_active = 1 where is_active = null");
		}
		// Columns added since version 5
		query = db.rawQuery("select * from divisions", null);
		if ( query.getColumnCount() < 6 ) {
			db.execSQL("alter table divisions add is_active integer;");
			db.execSQL("update divisions set is_active = 1 where is_active = null");
		}
		query = db.rawQuery("select * from subjects", null);
		if ( query.getColumnCount() < 4 ) {
			db.execSQL("alter table subjects add is_active integer;");
			db.execSQL("update subjects set is_active = 1 where is_active = null");
		}
		
		closeDB();
	}
	
	/**
	 * Opens database connection.
	 */
	private void openDB() {
		db = context.openOrCreateDatabase(DATABASE_NAME, Context.MODE_PRIVATE, null);
	}
	
	/**
	 * Closes database connection.
	 */
	private void closeDB() {
		db.close();
	}
	
	/**
	 * Executes a simple query to select info from database.
	 * 
	 * @param query the selection query
	 * @return a table storing rows and columns
	 */
	public ArrayList<ArrayList<String>> query(String query) {
		Cursor cursor = null;
		ArrayList<ArrayList<String>> result = new ArrayList<ArrayList<String>>();
		
		openDB();
		cursor = db.rawQuery(query, null);
		while ( cursor.moveToNext() ) {
			ArrayList<String> columns = new ArrayList<String>();
			for ( int i = 0; i < cursor.getColumnCount(); i++ ) {
				columns.add(cursor.getString(i));
			}
			result.add(columns);
		}
		closeDB();
		return result;
	}
	
	/**
	 * Inserts some values in a table.
	 * 
	 * @param table the target table
	 * @param cv the values
	 */
	public void insert(String table, ContentValues cv) {
		openDB();
		db.insert(table, null, cv);
		closeDB();
	}
	
	/**
	 * Deletes some rows from a table.
	 * 
	 * @param table the target table
	 * @param where the WHERE part of the query
	 * @param params some parameters for WHERE part
	 */
	public void delete(String table, String where, String[] params) {
		openDB();
		db.delete(table, where, params);
		closeDB();
	}
	
	/**
	 * Executes an update instruction.
	 * 
	 * @param update the instruction
	 */
	public void update(String update) {
		openDB();
		db.execSQL(update);
		closeDB();
	}
	
	/**
	 * Extracts the names of all columns from a table.
	 * 
	 * @param table the target table
	 * @return a list of column names
	 */
	public ArrayList<String> getTableColumnNames(String table) {
		ArrayList<String> columnNames = new ArrayList<String>();
		
		openDB();
		Cursor cursor = db.rawQuery("Select * from " + table, null);
		for ( int i = 0; i < cursor.getColumnCount(); i++ ) {
			columnNames.add(cursor.getColumnName(i));
		}
		closeDB();
		
		return columnNames;
	}
	
}
